package kr.co.amail.action;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Properties;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import kr.co.amail.bean.AutoAnylPoll0Bean;
import kr.co.amail.common.db.SqlComposer;
import kr.co.amail.common.db.SqlFactory;
import kr.co.amail.common.util.DBUtil;
import kr.co.amail.common.util.Page;
import kr.co.amail.common.util.StringUtil;
import kr.co.amail.ems.base.BaseAction;
import kr.co.amail.form.AutoAnylPoll0Form;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

public class AutoAnylPollAction extends BaseAction{
	
	public ActionForward work(ActionMapping mapping, ActionForm form,
			HttpServletRequest req, HttpServletResponse res, Connection con) 
		throws Exception {
		
		AutoAnylPoll0Form actionForm = (AutoAnylPoll0Form) form;
		
		
		//query
		String QUERY_SELECT_AUTO_ANYL_POLL = SqlFactory.getQuery( "AutoAnylPoll" , "QUERY_SELECT_AUTO_ANYL_POLL" );
		
		String m_sQUERY_AUTO_ANYL_POLL_DEFAULT1 = SqlFactory.getQuery( "AutoAnylPoll" , "m_sQUERY_AUTO_ANYL_POLL_DEFAULT1" );
		String m_sQUERY_AUTO_ANYL_POLL_DEFAULT2 = SqlFactory.getQuery( "AutoAnylPoll" , "m_sQUERY_AUTO_ANYL_POLL_DEFAULT2" );
		
		String m_sQUERY_AUTO_ANYL_POLL_SUB1 = SqlFactory.getQuery( "AutoAnylPoll" , "m_sQUERY_AUTO_ANYL_POLL_SUB1" );
		String m_sQUERY_AUTO_ANYL_POLL_SUB2 = SqlFactory.getQuery( "AutoAnylPoll" , "m_sQUERY_AUTO_ANYL_POLL_SUB2" );
		String m_sQUERY_AUTO_ANYL_POLL_SUB3 = SqlFactory.getQuery( "AutoAnylPoll" , "m_sQUERY_AUTO_ANYL_POLL_SUB3" );
		String m_sQUERY_AUTO_ANYL_POLL_SUB4 = SqlFactory.getQuery( "AutoAnylPoll" , "m_sQUERY_AUTO_ANYL_POLL_SUB4" );
		
		String m_sQUERY_SELECT_LASTWEEKDAY = SqlFactory.getQuery( "AutoAnylPoll" , "m_sQUERY_SELECT_LASTWEEKDAY1" );
		
		//actionForm.setTest_mail_id(sMailID);		
		Properties loginInfo = getLoginInfo(req);
		String sUserID = loginInfo.getProperty("USER_ID");
		String sUserType = loginInfo.getProperty("USER_GRADE1");
		String sUserName = loginInfo.getProperty("USER_NAME");
		String sUserDept = loginInfo.getProperty("USER_DEPT");
		String sUserDeptName = loginInfo.getProperty("EMPDEPTNAME");
		// End
		
		/// Query �ㅽ뻾 愿�젴 蹂�닔
		Properties SQL_PARAM = new Properties();
		Properties SQL_RESULT = new Properties();
		SqlComposer SQL_COMPOSER = SqlComposer.getInstance();
		
		String sCondSYYYY = StringUtil.validString(actionForm.get( "start_yyyy"));
		String sCondSMM = StringUtil.validString(actionForm.get( "start_mm"));
		String sCondSDD = StringUtil.validString(actionForm.get( "start_dd"));
		String sCondEYYYY = StringUtil.validString(actionForm.get( "end_yyyy"));
		String sCondEMM = StringUtil.validString(actionForm.get( "end_mm"));
		String sCondEDD = StringUtil.validString(actionForm.get( "end_dd"));

		String sInitUserType = actionForm.get( "start_dd");
		if( StringUtil.isNull(sInitUserType) ) {
			SQL_RESULT.clear();
			
			DBUtil.putSingleResultToMap(con,m_sQUERY_SELECT_LASTWEEKDAY,SQL_RESULT);    		
			
			String m_sLastWeek = SQL_RESULT.getProperty( "LAST_WEEK" );
			String m_sToday = SQL_RESULT.getProperty( "TODAY" );
			
			sCondSYYYY = m_sLastWeek.substring(0,4);
			sCondSMM	 = m_sLastWeek.substring(4,6);
			sCondSDD	 = m_sLastWeek.substring(6,8);
			if( sCondSMM.length() < 2 ) sCondSMM = "0"+sCondSMM;
			if( sCondSDD.length() < 2 ) sCondSDD = "0"+sCondSDD;
			
			sCondEYYYY = m_sToday.substring(0,4);
			sCondEMM   = m_sToday.substring(4,6);
			sCondEDD   = m_sToday.substring(6,8);
			if( sCondEMM.length() < 2 ) sCondEMM = "0"+sCondEMM;
			if( sCondEDD.length() < 2 ) sCondEDD = "0"+sCondEDD;
			
			// JSP 異쒕젰 媛��뗮똿...
			actionForm.set("start_yyyy",sCondSYYYY);
			actionForm.set("start_mm",sCondSMM);			
			actionForm.set("start_yyyy",sCondSYYYY);
			actionForm.set("start_mm",sCondSMM);
			actionForm.set("start_dd",sCondSDD);
			actionForm.set("end_yyyy",sCondEYYYY);
			actionForm.set("end_mm",sCondEMM);
			actionForm.set("end_dd",sCondEDD);
		}		
		
		//SQL_PARAM.setProperty("USER_ID", sUserID);
		//SQL_PARAM.setProperty("MAIL_ID", mail_id);
		
		SQL_COMPOSER.addCondition(SqlComposer.CONDITION_AND , m_sQUERY_AUTO_ANYL_POLL_DEFAULT1 );
		SQL_PARAM.setProperty("START_DATE", sCondSYYYY+sCondSMM+sCondSDD);
		SQL_COMPOSER.addCondition(SqlComposer.CONDITION_AND , m_sQUERY_AUTO_ANYL_POLL_DEFAULT2 );
		SQL_PARAM.setProperty("END_DATE", sCondEYYYY+sCondEMM+sCondEDD);
		
		String mail_type = actionForm.get("mail_type");
		String mail_type_seq = actionForm.get("mail_type_seq");
		String channel_code = actionForm.get("channel_code");
		String mail_type_desc = actionForm.get("mail_type_desc");
		
		if( !StringUtil.isNull(mail_type) ) {
			SQL_PARAM.setProperty("JOIN_TABLE", ", EMS_AUTO_SCHEDULE_INFO B");
			SQL_COMPOSER.addCondition(SqlComposer.CONDITION_AND , m_sQUERY_AUTO_ANYL_POLL_SUB1 );
			SQL_PARAM.setProperty( "MAIL_TYPE", mail_type );
			SQL_COMPOSER.addCondition(SqlComposer.CONDITION_AND , m_sQUERY_AUTO_ANYL_POLL_SUB2 );
		} else {
			SQL_PARAM.setProperty("JOIN_TABLE", "");
		}
		
		if( !StringUtil.isNull(mail_type_seq) ) {
			SQL_PARAM.setProperty( "MAIL_TYPE_SEQ", mail_type_seq );
			SQL_COMPOSER.addCondition(SqlComposer.CONDITION_AND , m_sQUERY_AUTO_ANYL_POLL_SUB3 );
		}
		if( !StringUtil.isNull(channel_code) ) {
			SQL_PARAM.setProperty( "CHANNEL_CODE", channel_code );
			SQL_COMPOSER.addCondition(SqlComposer.CONDITION_AND , m_sQUERY_AUTO_ANYL_POLL_SUB4 );
		}
		
		SQL_COMPOSER.addGroupBy( "A.WORKDAY" );
		SQL_COMPOSER.addOrderBy( SqlComposer.ORDER_ASC , "A.WORKDAY" );
		
		String sQuery_PollList = StringUtil.parseSQL( SQL_COMPOSER.getQuery( QUERY_SELECT_AUTO_ANYL_POLL , SqlComposer.EXEC_LIST_SELECT ) ,SQL_PARAM );

		///// [ init paging ]
		int nPage = 0;
		int nRows = 0;
		try {
			nRows = Integer.parseInt( actionForm.get("rows") );
		}
		catch(NumberFormatException e) {
			nRows = 10;
		}
		// End

		try {
			nPage = Integer.parseInt(actionForm.get("page") );
		}
		catch(NumberFormatException e) {
			nPage = 1;
		}
		// End
		Page page = new Page(nPage,nRows,10);
		//list
		ArrayList result = new ArrayList();
		DBUtil.executeQuery(con,StringUtil.parseSQL(sQuery_PollList, SQL_PARAM),result,new AutoAnylPoll0Bean(),page );
		
		// 議고쉶 寃곌낵瑜�JSON �뺥깭濡�留뚮벉.
		StringBuffer outSb = new StringBuffer();
		if (result.size() > 0) {
			outSb.append("[");
			for(int i=0; i<result.size(); i++) {
				AutoAnylPoll0Bean bean = (AutoAnylPoll0Bean)result.get(i);
				outSb.append("{\"Workday\":\"" + bean.getWorkday() + "\",");
				outSb.append("\"AvgStr\":\"" + bean.getAvg_str() + "\",");
				outSb.append("\"Cnt\":\"" + bean.getCnt() + "\",");
				outSb.append("\"AvgValue\":\"" + bean.getAvg_value() + "\"},");
			}
			outSb = outSb.replace(outSb.length()-1,outSb.length(), "");
			outSb.append("]");
		} else {
			outSb.append("");		
		}
		//System.out.println("outSb.toString() :: " + outSb.toString());
		req.setAttribute("chart_info",outSb.toString());
		
		req.setAttribute("result", result);
		req.setAttribute("pageObject",page);
		
		
		// excel파일로 뽑을 날짜 지정(해당 날짜로 데이터를 뽑는다.)
		req.setAttribute("startdate", sCondSYYYY+sCondSMM+sCondSDD);
		req.setAttribute("enddate", sCondEYYYY+sCondEMM+sCondEDD);

		
		
		
		
		return (mapping.getInputForward());
	}
}
